package ecustom.common.conn;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import ecustom.common.CustomMap;
import ecustom.common.util.CustomUtil;
import weaver.conn.RecordSet;
import weaver.conn.WeaverConnection;

/**
 * 数据库连接公共类。
 * 法律声明：非公开代码，仅供授权机构（公司或个人）使用，公开发表将追究法律责任。
 * @author 曹水涛
 */
public class DBServer extends DB {
	
	private static final Logger log = Logger.getLogger(DBServer.class);
	
	private String dsName;
	private WeaverConnection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	private ResultSetMetaData metaData;
	private String[] columnLabels;

	public DBServer() {}
	
	public DBServer(String dsName) {
		this.dsName = dsName;
	}
	
	public static DBServer conn() {
		return new DBServer();
	}
	
	public static DBServer conn(String dsName) {
		return new DBServer(dsName);
	}

	public CustomMap keyMap(String keyField, String sql, Object... whereValues) throws Exception {
		List<CustomMap> mapList = list(sql, whereValues);
		
		CustomMap keyMap = new CustomMap();
		if (mapList.isEmpty()) return keyMap;
		
		keyField = keyField.toUpperCase();
		
		for (CustomMap map : mapList) {
			String keyValue = CustomUtil.getString(map.get(keyField));
			if (CustomUtil.isBlank(keyValue)) continue ;
			keyMap.put(keyValue, map);
		}
		
		return keyMap;
	}

	public CustomMap one(String sql, Object... values) throws Exception {
		try {
			_executeQuery(sql, values);
			return rs.next() ? DBUtil.toMap(rs) : new CustomMap();
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public List<CustomMap> list(StringBuilder sql, Object... values) throws Exception {
		return list(sql.toString(), values);
	}

	public List<CustomMap> list(String sql, Object... values) throws Exception {
		List<CustomMap> list = new ArrayList<CustomMap>();
		try {
			_executeQuery(sql, values);
			while (rs.next()) {
				list.add(DBUtil.toMap(rs));
			}
			return list;
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public List<String> getStringList(String sql, Object... values) throws Exception {
		List<String> list = new ArrayList<String>();
		try {
			_executeQuery(sql, values);
			while (rs.next()) {
				list.add(rs.getString(1));
			}
			return list;
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public <T> T find(Class<T> clazz, String whereSql, Object... values) throws Exception {
		return findBySql(clazz, DBUtil.getQuerySql(clazz, whereSql), values);
	}

	public <T> T findBySql(Class<T> clazz, String sql, Object... values) throws Exception {
		if (CustomUtil.isBlank(this.dsName)) {
			this.dsName = DBUtil.getDsName(clazz);
		}
		try {
			_executeQuery(sql, values);
			return rs.next() ? DBUtil.toObject(rs, clazz, DBUtil.getQueryFields(clazz)) : null;
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public int save(Object o) throws Exception {
		CustomMap saveFields = DBUtil.getSaveFields(o);

		Set<String> fieldNames = saveFields.keySet();
		String sql = DBUtil.getInsertSql(fieldNames, o);
		
		Object[] values = new Object[fieldNames.size()];
		int valueIndex = 0;
		for (String fieldName : fieldNames) {
			values[valueIndex] = saveFields.get(fieldName);
			valueIndex++;
		}
		
		this.dsName = DBUtil.getDsName(o.getClass());
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public int save(String tableName, Map<String, Object> fieldsMap) throws Exception {
		Set<String> fieldNames = fieldsMap.keySet();
		String sql = DBUtil.getInsertSql(tableName, fieldNames);
		
		Object[] values = new Object[fieldNames.size()];
		int valueIndex = 0;
		for (String fieldName : fieldNames) {
			values[valueIndex] = fieldsMap.get(fieldName);
			valueIndex++;
		}
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public void saveCatch(Object o) throws Exception {
		try {
			CustomMap saveFields = DBUtil.getSaveFields(o);

			Set<String> fieldNames = saveFields.keySet();
			String sql = DBUtil.getInsertSql(fieldNames, o);

			Object[] values = new Object[fieldNames.size()];
			int valueIndex = 0;
			for (String fieldName : fieldNames) {
				values[valueIndex] = saveFields.get(fieldName);
				valueIndex++;
			}

			this.dsName = DBUtil.getDsName(o.getClass());
			_executeUpdate(sql, values);
		} catch (Exception e) {
			log.error("saveCatch()", e);
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public int update(Object o) throws Exception {
		CustomMap saveFields = DBUtil.getSaveFields(o);
		String idFieldName = DBUtil.getIdFieldName(o.getClass()).toUpperCase();
		Set<String> fieldNames = saveFields.keySet();
		fieldNames.remove(idFieldName);	// 移去ID字段

		String sql = DBUtil.getUpdateSql(fieldNames, o, idFieldName);
		
		Object[] values = new Object[fieldNames.size() + 1];
		int valueIndex = 0;
		for (String fieldName : fieldNames) {
			values[valueIndex++] = saveFields.get(fieldName);
		}
		values[valueIndex] = DBUtil.getIdFieldValue(o);
		
		this.dsName = DBUtil.getDsName(o.getClass());
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public int update(String tableName, Map<String, Object> updateFields, String whereSql, Object... whereValues) throws SQLException {
		String sql = "UPDATE " + tableName + " SET %s WHERE " + whereSql;
		
		whereValues = whereValues == null ? new Object[] {} : whereValues;
		
		Object[] values = new Object[updateFields.size() + whereValues.length];
		int index = 0;
		String sqlSet = "";
		for (String key : updateFields.keySet()) {
			values[index++] = updateFields.get(key);
			sqlSet += ", " + key + "=?";
		}
		for (Object value : whereValues) {
			values[index++] = value;
		}
		
		sql = String.format(sql, sqlSet.substring(2));
		return DBServer.conn().executeUpdate(sql, values);
	}
	
	public int delete(String sql, Object... values) throws Exception {
		if (CustomUtil.isBlank(this.dsName)) {
			this.dsName = DBUtil.getDsName(Integer.class);
		}
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public <T> List<T> searchAll(Class<T> clazz) throws Exception {
		return searchBySql(clazz, DBUtil.getQuerySql(clazz, ""));
	}

	public <T> List<T> search(Class<T> clazz, String whereSql, Object... values) throws Exception {
		return searchBySql(clazz, DBUtil.getQuerySql(clazz, whereSql), values);
	}

	public <T> List<T> searchBySql(Class<T> clazz, String sql, Object... values) throws Exception {
		List<T> list = new ArrayList<T>();
		if (CustomUtil.isBlank(this.dsName)) {
			this.dsName = DBUtil.getDsName(clazz);
		}
		try {
			_executeQuery(sql, values);
			while (rs.next()) {
				list.add(DBUtil.toObject(rs, clazz, DBUtil.getQueryFields(clazz)));
			}
		} finally {
			DBUtil.returnConnection(this);
		}
		return list;
	}
	
	public List<Integer> getIntegerList(String sql, Object... values) throws Exception {
		List<Integer> list = new ArrayList<Integer>();
		try {
			_executeQuery(sql, values);
			while (rs.next()) {
				list.add(CustomUtil.getInteger(rs.getString(1)));
			}
			return list;
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public List<Integer> getIntegerList(StringBuilder sql, Object... values) throws Exception {
		return getIntegerList(sql.toString(), values);
	}
	
	public List<String> getStringList(StringBuilder sql, Object... values) throws Exception {
		return getStringList(sql.toString(), values);
	}
	
	public Integer getInteger(StringBuilder sql, Object... values) throws Exception {
		return getInteger(sql.toString(), values);
	}

	public Integer getInteger(String sql, Object... values) throws Exception {
		return findBySql(Integer.class, sql, values);
	}
	
	public Double getDouble(String sql, Object... values) throws Exception {
		return findBySql(Double.class, sql, values);
	}
	
	public Double getDouble(StringBuilder sql, Object... values) throws Exception {
		return findBySql(Double.class, sql.toString(), values);
	}
	
	public String getSource() {
		if (CustomUtil.isBlank(this.dsName)) {
			this.dsName = "ecology";
		}
		return this.dsName;
	}

	public WeaverConnection getConnection() throws SQLException {
		if (this.conn != null) {
			return this.conn;
		}
		this.conn = DBUtil.getConnection(this.dsName);
		return this.conn;
	}

	public PreparedStatement getStatement() {
		return stmt;
	}

	public ResultSet getResultSet() {
		return rs;
	}
	
	private ResultSet _executeQuery(String sql, Object... values) throws Exception {
		log.debug("executeQuery(), sql = " + sql + ", " + DBUtil.joinValues(values));
		stmt = getConnection().prepareStatement(sql);
		DBUtil.setParameters(this.stmt, values);
		rs = stmt.executeQuery();
		this.metaData = rs.getMetaData();
		setColumnLabels();
		return rs;
	}
	
	private int _executeUpdate(String sql, Object... values) throws SQLException {
		log.debug("executeUpdate(), sql = " + sql + ", " + DBUtil.joinValues(values));
		if ("ecology".equals(getSource())) {
			if (!new RecordSet().executeUpdate(sql, values)) {
				throw new SQLException("执行失败，sql = " + sql);
			}
			return 1;
		}
		stmt = getConnection().prepareStatement(sql);
		DBUtil.setParameters(this.stmt, values);
		return stmt.executeUpdate();
	}

	public int executeUpdate(String sql, Object... values) throws SQLException {
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public CustomMap map(StringBuilder sql, Object... whereValues) throws Exception {
		return one(sql.toString(), whereValues);
	}

	public String getString(String sql, Object... values) throws Exception {
		return findBySql(String.class, sql, values);
	}

	public ResultSetMetaData getMetaData() {
		return metaData;
	}

	public String[] getColumnLabels() {
		return columnLabels;
	}
	
	private void setColumnLabels() throws Exception {
		int count = metaData.getColumnCount();
		this.columnLabels = new String[count];
		for (int i = 0; i < count; ) {
			columnLabels[i++] = metaData.getColumnLabel(i);
		}
	}
	
}